#!/usr/bin/python3
import configparser
from tokenize import String

import pymysql
from pymysql.cursors import DictCursor


class DBHelp:
    def __init__(self):
        print("DBHelp")

    @classmethod
    def conn(cls):
        # 打开数据库连接
        global db
        file = './config/config.ini'
        con = configparser.ConfigParser()
        con.read(file, encoding='utf-8')
        items = con.items('mysql')
        items = dict[any, any](items)
        db = pymysql.connect(host=items.get('host'),
                             user=items.get('username'),
                             password=items.get('password'),
                             database=items.get('database'),
                             port=items.get('3306'))
        if db is not None:
            print("数据库连接成功")
        else:
            print("数据库连接失败")

    @classmethod
    async def close(cls):
        # 关闭数据库连接
        db.close()

    @classmethod
    def selectRows(cls, sql, arg: []):
        print(sql)
        cursor = db.cursor(cursor=DictCursor)
        cursor.execute(sql, arg)
        data = cursor.fetchall()
        cursor.close()
        print(f"查询到{cursor.rowcount}条数据")
        return data

    @classmethod
    def insertRow(cls,  table: String, column: [], values: [], where="", ):
        # 拼接sql
        __column__ = ', '.join(column)
        __values__ = []
        for e in column:
            __values__.append("%s")
        sql = f"INSERT INTO {table} ({__column__}) VALUES({', '.join(__values__)})"
        if where != "":
            sql += " " + where
        print(sql)
        cursor = db.cursor(cursor=DictCursor)
        cursor.execute(sql, values)
        db.commit()
        print(f"插入{cursor.rowcount}条数据")
        return cursor.lastrowid
